﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IsmsExplorer.Model.Projects;
using IsmsExplorer.Infrastructure.Helper;

namespace IsmsExplorer.Infrastructure.Repositories {
    internal class ProjectRepository : SqlServerRepositoryBase<Project>, IProjectRepository {
        #region Data
        #endregion
        
        #region Constructors
        public ProjectRepository(IUnitOfWork unitOfWork) : base(unitOfWork) {
        }

        public ProjectRepository() : this(null) {
        }
        #endregion
        
        #region IProjectRepository members
        
        public Project FindBy(string name) {
            StringBuilder sb = new StringBuilder(GetBaseQuery());
            return this.BuildEntityFromSql(sb.AppendFormat(" WHERE P.{0}=N'{1}'", ProjectFactory.FieldNames.Name, name).Append(";").ToString());
        }

        #endregion
        
        #region BuildChildDataCallbacks
        protected override void InitialiseChildCallbacks() {
            // Do nothing, no callbacks yet as no foreign keys in Projects table.
        }
        #endregion
        
        #region GetBaseQuery        
        protected override string GetBaseQuery() {
            return string.Format(@"SELECT {0},{1},{2} FROM [Projects] P", 
                ProjectFactory.FieldNames.ID,
                ProjectFactory.FieldNames.Name,
                ProjectFactory.FieldNames.StartDate);
        }
        #endregion

        #region GetBaseWhereClause
        protected override string GetBaseWhereClause() {
            StringBuilder sb = new StringBuilder(@"WHERE ").Append(ProjectFactory.FieldNames.ID).Append("={0}");
            return sb.ToString();
        }
        #endregion
        
        #region UnitOfWork implementation
        protected override void PersistDeletedItem(Project item) {
            // NOTE: currently database is set up so that deleting asset project removes also 
            //       all dependend rows (CASCADE DELETE) in database. May want to change 
            //       this and delete manually.
            string sql = string.Format(@"DELETE FROM [Projects] {0}", this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sql));
        }

        protected override void PersistNewItem(Project item) {
            StringBuilder sb = new StringBuilder(256);
            sb.AppendFormat(@"INSERT INTO [Projects]({0},{1}) VALUES ({2},{3}); SELECT SCOPE_IDENTITY();",
                ProjectFactory.FieldNames.Name, ProjectFactory.FieldNames.StartDate,
                DataHelper.GetSqlValue(item.Name), DataHelper.GetSqlValue(item.StartDate));
            item.ID = Convert.ToInt32(this.Database.ExecuteScalar(this.Database.GetSqlStringCommand(sb.ToString())));
            //this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));            
        }

        protected override void PersistUpdatedItem(Project item) {
            StringBuilder sb = new StringBuilder(@"UPDATE [Projects] SET ");
            sb.AppendFormat("{0}={1},", ProjectFactory.FieldNames.Name, DataHelper.GetSqlValue(item.Name));
            sb.AppendFormat("{0}={1},", ProjectFactory.FieldNames.StartDate, DataHelper.GetSqlValue(item.StartDate));
            // Remove the last unnecessary comma character!
            sb.Length -= ",".Length;
            sb.Append(" ").Append(this.BuildBaseWhereClause(item.ID));
            this.Database.ExecuteNonQuery(this.Database.GetSqlStringCommand(sb.ToString()));            
        }
        #endregion
        
        #region Private helper/callback methods
        #endregion
        
    } // end public class ProjectRepository : SqlServerRepositoryBase<Project>, IProjectRepository
}
